

'==============================
' DataFrame get set
'==============================
Private Sub test_DataFrame_get_set()
    Dim df As IDataFrame
    Dim realDF As DataFrame
    Set realDF = New DataFrame
    realDF.Init colCount:=10, initRowSize:=100
    
    Set df = realDF
    df.appendEmptyRow 20
    
    Dim ri As Long
    Dim ci As Integer
    For ri = 0 To 19
        For ci = 0 To 9
            df.setDataByColIndex ri, ci, "c" & ri & ci
        Next
    Next
    
    For ri = 0 To 19
        For ci = 0 To 9
            Debug.Assert df.getDataByColIndex(ri, ci) = "c" & ri & ci
        Next
    Next
End Sub



'==============================
' DataFrame slice set
'==============================
Private Sub test_DataFrame_slice_set()
    Dim df As IDataFrame
    Set df = getStringDF(20, 20)
    
    Dim rowIndexes() As Variant
    Dim colIndexes() As Variant
    rowIndexes = Array(5, 4, 3, 2, 1)
    colIndexes = Array(1, 3, 5)
    
    Dim dfSlice As IDataFrame
    Set dfSlice = df.slice(rowIndexes:=rowIndexes, colIndexes:=colIndexes)
    
    dfSlice.setDataByColIndex 0, 0, "xx"
    Debug.Print dfSlice.getDataByColIndex(0, 0) = df.getDataByColIndex(5, 1)
    Debug.Print df.getDataByColIndex(5, 1) = "xx"
End Sub

'==============================
' DataFrame slice append
'==============================
Private Sub test_DataFrame_slice_append()
    Dim df As IDataFrame
    Set df = getStringDF(20, 20)
    
    Dim rowIndexes() As Variant
    Dim colIndexes() As Variant
    rowIndexes = Array(5, 4, 3, 2, 1)
    colIndexes = Array(1, 3, 5)
    
    Dim dfSlice As IDataFrame
    Set dfSlice = df.slice(rowIndexes:=rowIndexes, colIndexes:=colIndexes)
    dfSlice.appendEmptyRow
    dfSlice.setDataByColIndex 5, 0, "xx"
    
    Debug.Print dfSlice.size = 6
    Debug.Print df.size = 21
    
    Debug.Print dfSlice.getDataByColIndex(5, 0) = df.getDataByColIndex(20, 1)
    Debug.Print df.getDataByColIndex(20, 1) = "xx"
End Sub

'==============================
' DataFrame slice copyAndAppendRow
'==============================
Private Sub test_DataFrame_slice_copyAndAppendRow()
    Dim df As IDataFrame
    Set df = getStringDF(20, 20)
    
    Dim rowIndexes() As Variant
    Dim colIndexes() As Variant
    rowIndexes = Array(5, 4, 3, 2, 1)
    colIndexes = Array(1, 3, 5)
    
    Dim dfSlice As IDataFrame
    Set dfSlice = df.slice(rowIndexes:=rowIndexes, colIndexes:=colIndexes)
    
    dfSlice.copyAndAppendRow 1
    
    Debug.Print dfSlice.size = 6
    Debug.Print df.size = 21
    
    Debug.Print dfSlice.getDataByColIndex(5, 0) = df.getDataByColIndex(20, 1)
    Debug.Print dfSlice.getDataByColIndex(5, 0) = "c41"
    Debug.Print df.getDataByColIndex(20, 1) = "c41"
    
End Sub

'==============================
' DataFrame slice delCol
'==============================
Private Sub test_DataFrame_slice_delCol()
    Dim df As IDataFrame
    Set df = getStringDF(20, 20)
    
    Dim rowIndexes() As Variant
    Dim colIndexes() As Variant
    rowIndexes = Array(5, 4, 3, 2, 1)
    colIndexes = Array(1, 3, 5)
    
    Dim dfSlice As IDataFrame
    Set dfSlice = df.slice(rowIndexes:=rowIndexes, colIndexes:=colIndexes)
    
    ' delete slice 1, delete df 3
    dfSlice.delCol 1
    
    Debug.Print dfSlice.columnCount = 2
    Debug.Print df.columnCount = 20
End Sub

'==============================
' DataFrame slice deepDelCol
'==============================
Private Sub test_DataFrame_slice_deepDelCol()
    Dim df As IDataFrame
    Set df = getStringDF(20, 20)
    
    Dim rowIndexes() As Variant
    Dim colIndexes() As Variant
    rowIndexes = Array(5, 4, 3, 2, 1)
    colIndexes = Array(1, 3, 5)
    
    Dim dfSlice As IDataFrame
    Set dfSlice = df.slice(rowIndexes:=rowIndexes, colIndexes:=colIndexes)
    
    ' delete slice 1, delete df 3
    dfSlice.deepDelCol 1
    
    Debug.Print dfSlice.columnCount = 2
    Debug.Print df.columnCount = 19
    Debug.Print df.getDataByColIndex(0, 3) = "c04"
End Sub

'==========================
' write
'==========================
Private Sub test_DataFramme_write_sheet()
    Dim df As IDataFrame
    Set df = getStringDF(20, 20)
    
    df.printToWorksheet Sheets("Sheet9"), dataDescriptorHandler:="Test___DataFrame.myDataDescriptorHandler"
End Sub

Private Sub test_DataFrammeSlice_write_sheet()
    Dim df As IDataFrame
    Set df = getStringDF(20, 20)
    
    Dim dfSlice As IDataFrame
    'Set dfSlice = df.slice(rowStart:=2, rowStop:=10, colStart:=3, colStep:=7)
    Set dfSlice = df.slice(rowStart:=2, rowStop:=10, colStart:=3, colStop:=7)
    
    Dim dataDescriptor As IDFDescriptor
    Set dataDescriptor = dfSlice.getDataDescriptor(2, 3, True)
    If Not dataDescriptor Is Nothing Then
        dataDescriptor.Color = 5296274
    End If
    
    dfSlice.printToWorksheet Sheets("Sheet9"), dataDescriptorHandler:="Test___DataFrame.myDataDescriptorHandler"
End Sub

Private Sub myDataDescriptorHandler(ByRef ctx As DFSheetWriterContext, ByRef descriptor As IDFDescriptor)
    If descriptor Is Nothing Then
        Exit Sub
    End If
    
    Dim sheetCell As Range
    Set sheetCell = ctx.activeSheetDataCell

    sheetCell.Interior.Color = descriptor.Color
    'Debug.Print ctx.CurrentSheetRowNo
End Sub




'==============================
' DataFrame filter
'==============================
Private Sub test_DataFrame_filter_single_df()
    Dim df As IDataFrame
    Set df = getStringDF(20, 20)
    
    Dim realDF As DataFrame
    Set realDF = df
    
    Dim f1 As DFFilter
    Set f1 = df.filter
    
    'Dim v As Variant
    'For Each v In df.ColumnNames
    '    Debug.Print v
    'Next
    Dim filterResult As IDataFrame
    Set filterResult = f1.where() _
                         .whereAND(f1.col("0").eq("c00")) _
                         .whereOR(DFHelper.cAND(f1.col("1").eq("c81"))) _
                        .exec
    
    Set realDF = filterResult
    Debug.Print filterResult.size
    
    filterResult.printToConsole
End Sub

' A left B
Private Sub test_DataFrame_filter_df_left_join()
    Dim sheetDF1 As DataFrame
    Set sheetDF1 = New DataFrame
    sheetDF1.InitBySheet Sheets("dffilter").Range("A2:D9"), _
                        Array(DF_DataType.DF_STRING, DF_DataType.DF_INT, DF_DataType.DF_STRING, DF_DataType.DF_DATE), _
                        colNameRange:=Sheets("dffilter").Range("A1:D1")
                        
    Dim sheetDF2 As DataFrame
    Set sheetDF2 = New DataFrame
    sheetDF2.InitBySheet Sheets("dffilter").Range("A13:D20"), _
                        Array(DF_DataType.DF_STRING, DF_DataType.DF_INT, DF_DataType.DF_STRING, DF_DataType.DF_STRING), _
                        colNameRange:=Sheets("dffilter").Range("A12:D12")

    Dim df1 As IDataFrame
    Dim df2 As IDataFrame
    Set df1 = sheetDF1
    Set df2 = sheetDF2
    
    
    Dim f1 As DFFilter
    Dim f2 As DFFilter
    Set f1 = df1.filter
    Set f2 = df2.filter
    
    Dim c As IDFFilterCol
    Set c = f2.col("a")
    f1.col("a").eq c
    
    Dim filterResult As IDataFrame
    Set filterResult = f1 _
        .Alias("t1") _
        .selectCol(f1.col("a")) _
        .selectCol(f1.col("b")) _
        .selectCol(f1.col("c")) _
        .selectCol(f2.col("c").Alias("cx")) _
        .selectCol(f2.col("d")) _
        .leftJoin(f2.Alias("t2")) _
        .joinON(f1.col("a").eq(f2.col("a"))) _
        .joinAND(f1.col("b").eq(f2.col("b"))) _
        .where() _
        .whereAND(f1.col("a").inVals(Array("a1", "a2", "a3", "a4", "a8"))) _
        .exec()
        
        
    filterResult.printToWorksheet _
        Sheets("Sheet10"), _
        dataDescriptorHandler:="Test___DataFrame.myDataDescriptorHandler"
End Sub

' A left B right C
Private Sub test_DataFrame_filter_df_right_join()
    Dim sheetDF1 As DataFrame
    Set sheetDF1 = New DataFrame
    sheetDF1.InitBySheet Sheets("dffilter").Range("A2:D9"), _
                        Array(DF_DataType.DF_STRING, DF_DataType.DF_INT, DF_DataType.DF_STRING, DF_DataType.DF_DATE), _
                        colNameRange:=Sheets("dffilter").Range("A1:D1")
                        
    Dim sheetDF2 As DataFrame
    Set sheetDF2 = New DataFrame
    sheetDF2.InitBySheet Sheets("dffilter").Range("A13:D20"), _
                        Array(DF_DataType.DF_STRING, DF_DataType.DF_INT, DF_DataType.DF_STRING, DF_DataType.DF_STRING), _
                        colNameRange:=Sheets("dffilter").Range("A12:D12")
                        
    Dim sheetDF3 As DataFrame
    Set sheetDF3 = New DataFrame
    sheetDF3.InitBySheet Sheets("dffilter").Range("A24:C25"), _
                        Array(DF_DataType.DF_STRING, DF_DataType.DF_INT, DF_DataType.DF_STRING), _
                        colNameRange:=Sheets("dffilter").Range("A23:C23")
           
           
    Dim df1 As IDataFrame
    Dim df2 As IDataFrame
    Dim df3 As IDataFrame
    Set df1 = sheetDF1
    Set df2 = sheetDF2
    Set df3 = sheetDF3
    
    
    Dim f1 As DFFilter
    Dim f2 As DFFilter
    Dim f3 As DFFilter
    Set f1 = df1.filter
    Set f2 = df2.filter
    Set f3 = df3.filter
    
    Dim c As IDFFilterCol
    Set c = f2.col("a")
    f1.col("a").eq c
    
    Dim filterResult As IDataFrame
    Set filterResult = f1 _
        .Alias("t1") _
        .selectCol(f1.col("a")) _
        .selectCol(f1.col("b")) _
        .selectCol(f1.col("c")) _
        .selectCol(f2.col("c").Alias("cx")) _
        .selectCol(f2.col("d")) _
        .selectCol(f3.col("c").Alias("c3")) _
        .leftJoin(f2.Alias("t2")) _
        .joinON(f1.col("a").eq(f2.col("a"))) _
        .joinAND(f1.col("b").eq(f2.col("b"))) _
        .rightJoin(f3.Alias("t3")) _
        .joinON(f3.col("a").eq(f2.col("a"))) _
        .joinAND(f3.col("b").eq(f2.col("b"))) _
        .exec()
        '.where() _
        '.whereAND(f1.col("a").inVals(Array("a1", "a2", "a3", "a4", "a5"))) _
        .exec()
        
        
    filterResult.printToWorksheet _
        Sheets("Sheet10"), _
        dataDescriptorHandler:="Test___DataFrame.myDataDescriptorHandler"
End Sub
